unit clsBackup_NotaCompra;

interface

uses Dm, dbtables, biblio, sysutils;

type
  TBackup_NotaCompra = class(TObject)
  public
    num_nota: string;
    function Existe_Nota(Notac_numero, Notc_Serie, Ent_Codigo: string; var bkp_nota: string): boolean;
    procedure Recuperar(bkp_nota, bkp_serie: string; var tq: TQuery);
    function RecuperarNotaNumero(bkp_nota, bkp_serie, ent_codigo: string): string;
    procedure RecuperarBackup(Notc_Serie, Notac_Numero, p_Ent_Codigo: string; var bkp_nota, ent_codigo: string);
    procedure Gravar(bkp_nota, bkp_serie, notac_numero, notc_serie, ent_codigo: string);
    procedure Excluir(bkp_nota, bkp_serie, ent_codigo: string);
    function Retorna_Nota(bkp_nota, bkp_serie: string; var nota1, nota2: string): boolean;
    function Retorna_Notas(bkp_nota, bkp_serie, ent_codigo: string; var nota1, nota2, serie: string): boolean;
    function Retorna_Notas2(bkp_nota, ent_codigo: string; var nota1, nota2: string): boolean;
    procedure ConsultarNotaFiscal(var p_q: TQuery; p_data: TDateTime; tipo: integer);
    procedure ConsultarConhecimentoTransporte(var p_q: TQuery; p_data: TDateTime; tipo: integer);
    function RetornaBck(serie, nota, entidade: string): string;
    function Ultima_nota: string;
  end;

implementation

procedure TBackup_NotaCompra.Gravar;
var
  tq: TQuery;
begin
  tq := Tquery.Create(DmInt);
  tq.databasename := dmint.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('Insert into Backup_NotaCompra values(:a,:b,:c,:d,:e)');
  tq.params[0].asstring := strzero(bkp_Nota, 9);
  tq.params[1].asstring := bkp_serie;
  tq.params[2].asstring := strzero(Notac_Numero, 9);
  tq.params[3].asstring := Notc_Serie;
  tq.params[4].asstring := strzero(ent_codigo, 5);
  GeraLog('BACKUP_NOTACOMPRA', 'Gravar');
  tq.execsql;
  tq.Free;
end;

procedure TBackup_NotaCompra.Excluir;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := dmint.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('Delete from Backup_NotaCompra where bkp_nota=:a and bkp_serie=:b and ' +
    'ent_codigo=:c');
  tq.params[0].asstring := strzero(bkp_nota, 9);
  tq.params[1].asstring := bkp_serie;
  tq.params[2].asstring := strzero(ent_codigo, 5);
  GeraLog('BACKUP_NOTACOMPRA', 'Excluir');
  tq.execSQL;
  tq.close;
  tq.Free;
end;

function TBackup_NotaCompra.Retorna_Notas;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := dmint.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('Select * from Backup_NotaCompra where bkp_nota=:a and bkp_serie=:b and ' +
    'ent_codigo=:c');
  tq.params[0].asstring := strzero(bkp_nota, 9);
  tq.params[1].asstring := bkp_serie;
  tq.params[2].asstring := strzero(ent_codigo, 5);
  tq.open;
  if not tq.eof then begin
    nota1 := tq.fieldbyname('Notac_Numero').asstring;
    serie := tq.fieldbyname('Notc_Serie').asstring;
    tq.next;
    nota2 := tq.fieldbyname('Notac_Numero').asstring;
    result := true;
  end else result := false;
  if nota1 = '' then begin
    nota1 := strzero(bkp_nota, 9);
    serie := bkp_serie;
  end;
  if nota2 = '' then nota2 := strzero(bkp_nota, 9);
  tq.close;
  tq.Free;
end;

procedure TBackup_NotaCompra.Recuperar;
begin
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('Select * from Backup_NotaCompra where bkp_nota=:a and bkp_serie=:b');
  tq.params[0].asstring := strzero(bkp_nota, 9);
  tq.params[1].asstring := bkp_serie;
  tq.open;
  num_nota := tq.FieldByName('Notac_Numero').AsString;
end;

function TBackup_NotaCompra.RecuperarNotaNumero(bkp_nota, bkp_serie, ent_codigo: string): string;
var
  tq: tquery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := dmint.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('Select * from Backup_NotaCompra where bkp_nota=:a and bkp_serie=:b and ent_codigo=:c');
  tq.params[0].asstring := strzero(bkp_nota, 9);
  tq.params[1].asstring := bkp_serie;
  tq.params[2].asstring := ent_codigo;
  tq.open;
  Result := tq.FieldByName('Notac_Numero').AsString;
  tq.Close;
  tq.Free;
end;

function TBackup_NotaCompra.Existe_Nota;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := dmint.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('Select * from Backup_NotaCompra where Notac_Numero=:a and Notc_Serie=:b and ' +
    'ent_codigo=:c');
  tq.params[0].asstring := strzero(Notac_Numero, 9);
  tq.params[1].asstring := Notc_Serie;
  tq.params[2].asstring := strzero(ent_codigo, 5);
  tq.open;
  bkp_nota := tq.fieldbyname('Bkp_Nota').asstring;
  if not tq.eof then result := true else result := false;
  tq.close;
  tq.Free;
end;

procedure TBackup_NotaCompra.RecuperarBackup;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.active := false;
  tq.sql.clear;
  tq.Params.clear;
  tq.sql.add('Select * from Backup_NotaCompra where Notc_Serie=:a and ' +
    'Notac_Numero=:b and Ent_Codigo=:c');
  tq.params[0].asstring := Notc_Serie;
  tq.params[1].asstring := Notac_Numero;
  tq.params[2].asstring := p_Ent_Codigo;
  tq.open;
  bkp_nota := tq.fieldbyname('bkp_nota').AsString;
  ent_codigo := tq.fieldbyname('ent_codigo').AsString;
  tq.close;
  tq.free;
end;

procedure TBackup_NotaCompra.ConsultarNotaFiscal;
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  if tipo = 2 then begin
    p_q.sql.add('Select b.Bkp_serie,b.Bkp_nota,b.ent_codigo,n.Notc_datafatura,e.ent_nome,' +
      'n.Imp_NotaFiscal from Backup_NotaCompra b,notacompra n, entidade e where ' +
      'n.Notc_datafatura>=:k_P_data and b.notc_serie=n.notc_serie and ' +
      'b.notac_numero=n.notac_numero and b.ent_codigo=n.ent_codigo and ' +
      'n.ent_codigo=e.ent_codigo group by b.Bkp_serie,b.Bkp_nota,b.ent_codigo,' +
      'n.Notc_datafatura,e.ent_nome,n.Imp_NotaFiscal');
    p_q.parambyname('k_P_data').AsDate := P_data;
  end else begin
    if tipo = 0 then begin
      p_q.sql.add('Select b.Bkp_serie,b.Bkp_nota,b.ent_codigo,n.Notc_datafatura,e.ent_nome,' +
        'n.Imp_NotaFiscal from Backup_NotaCompra b,notacompra n, entidade e where ' +
        'n.Notc_datafatura>=:k_P_data and n.Imp_NotaFiscal=:k_P_tipo and ' +
        'b.notc_serie=n.notc_serie and b.notac_numero=n.notac_numero and ' +
        'b.ent_codigo=n.ent_codigo and n.ent_codigo=e.ent_codigo group by ' +
        'b.Bkp_serie,b.Bkp_nota,b.ent_codigo,n.Notc_datafatura,e.ent_nome,n.Imp_NotaFiscal');
      p_q.parambyname('k_P_tipo').Asstring := 'S'
    end else begin
      p_q.sql.add('Select b.Bkp_serie,b.Bkp_nota,b.ent_codigo,n.Notc_datafatura,e.ent_nome,' +
        'n.Imp_NotaFiscal from Backup_NotaCompra b,notacompra n, entidade e where ' +
        'n.Notc_datafatura>=:k_P_data and n.Imp_NotaFiscal is null and ' +
        'b.notc_serie=n.notc_serie and b.notac_numero=n.notac_numero and ' +
        'b.ent_codigo=n.ent_codigo and n.ent_codigo=e.ent_codigo group by ' +
        'b.Bkp_serie,b.Bkp_nota,b.ent_codigo,n.Notc_datafatura,e.ent_nome,n.Imp_NotaFiscal');
    end;
    p_q.parambyname('k_P_data').AsDate := P_data;
  end;
end;

function TBackup_NotaCompra.Ultima_nota: string;
var
  tq: TQuery;
begin
  tq := TQuery.Create(dmint);
  tq.databasename := dmint.db.databasename;
  tq.active := false;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('Select max(notac_numero) ultimo from BACKUP_NOTACOMPRA');
  tq.open;
  if tq.fieldbyname('ultimo').asstring = '' then
    result := '000000001'
  else
    result := strzero(tq.fieldbyname('ultimo').asinteger + 1, 9);
  tq.close;
  tq.free;

end;

function TBackup_NotaCompra.Retorna_Notas2(bkp_nota, ent_codigo: string;
  var nota1, nota2: string): boolean;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := dmint.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('Select * from Backup_NotaCompra where bkp_nota=:a and ' +
    'ent_codigo=:c');
  tq.params[0].asstring := strzero(bkp_nota, 9);
  tq.params[1].asstring := strzero(ent_codigo, 5);
  tq.open;
  if not tq.eof then begin
    nota1 := tq.fieldbyname('Notac_Numero').asstring;
    tq.next;
    nota2 := tq.fieldbyname('Notac_Numero').asstring;
    result := true;
  end else result := false;
  if nota1 = '' then begin
    nota1 := strzero(bkp_nota, 9);
  end;
  if nota2 = '' then nota2 := strzero(bkp_nota, 9);
  tq.close;
  tq.Free;
end;

function TBackup_NotaCompra.Retorna_Nota(bkp_nota, bkp_serie: string;
  var nota1, nota2: string): boolean;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := dmint.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('Select * from Backup_NotaCompra where bkp_nota=:a and bkp_serie=:b ');
  tq.params[0].asstring := strzero(bkp_nota, 9);
  tq.params[1].asstring := bkp_serie;
  tq.open;
  if not tq.eof then begin
    nota1 := tq.fieldbyname('Notac_Numero').asstring;
    tq.next;
    nota2 := tq.fieldbyname('Notac_Numero').asstring;
    result := true;
  end else result := false;
  if nota1 = '' then begin
    nota1 := strzero(bkp_nota, 9);
  end;
  if nota2 = '' then nota2 := strzero(bkp_nota, 9);
  tq.close;
  tq.Free;

end;

function TBackup_NotaCompra.RetornaBck;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.Active := false;
  tq.SQL.Clear;
  tq.Params.Clear;
  tq.SQL.Add('Select BKP_NOTA,BKP_SERIE,NOTAC_NUMERO,NOTC_SERIE,ENT_CODIGO from backup_notacompra where NOTC_SERIE=:k_nota_serie and ' +
    'NOTAC_NUMERO=:K_nota_numero and ent_codigo =:k_entidade');
  tq.ParamByName('k_nota_serie').AsString := serie;
  tq.ParamByName('k_nota_numero').AsString := nota;
  tq.ParamByName('k_entidade').AsString := entidade;
  tq.Open;
  Result := tq.FieldByName('BKP_NOTA').AsString;
  tq.Close;
  tq.free;
end;

procedure TBackup_NotaCompra.ConsultarConhecimentoTransporte(
  var p_q: TQuery; p_data: TDateTime; tipo: integer);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  if tipo = 2 then begin
    p_q.sql.add('Select b.Bkp_serie,b.Bkp_nota,b.ent_codigo,n.Notc_datafatura,e.ent_nome,' +
      'n.Imp_NotaFiscal from Backup_NotaCompra b,notacompra n, entidade e where ' +
      'n.Notc_datafatura>=:k_P_data and b.notc_serie=n.notc_serie and ' +
      'b.notac_numero=n.notac_numero and b.ent_codigo=n.ent_codigo and ' +
      'n.ent_codigo=e.ent_codigo ' +
      '            and Codf_Codigo in ' +
      '                          (select codf_codigo ' +
      '                             from Modelo_CodigoFiscal ' +
      '                            where Mod_codigo in ' +
      '                                             (select Mod_codigo from ModeloNF ' +
      '                                               where Mod_Modelo like ' + QuotedStr('%CONHECIMENTO DE TRANSPORTE%') + ' ' +
      '                                                  or MOD_ESPECIE = ' + QuotedStr('CTRC') +
      '                                             ) ' +
      '                          )' +
      'group by b.Bkp_serie,b.Bkp_nota,b.ent_codigo,' +
      'n.Notc_datafatura,e.ent_nome,n.Imp_NotaFiscal');
    p_q.parambyname('k_P_data').AsDate := P_data;
  end else begin
    if tipo = 0 then begin
      p_q.sql.add('Select b.Bkp_serie,b.Bkp_nota,b.ent_codigo,n.Notc_datafatura,e.ent_nome,' +
        'n.Imp_NotaFiscal from Backup_NotaCompra b,notacompra n, entidade e where ' +
        'n.Notc_datafatura>=:k_P_data and n.Imp_NotaFiscal=:k_P_tipo and ' +
        'b.notc_serie=n.notc_serie and b.notac_numero=n.notac_numero and ' +
        'b.ent_codigo=n.ent_codigo and n.ent_codigo=e.ent_codigo '+
        '            and Codf_Codigo in ' +
        '                          (select codf_codigo ' +
        '                             from Modelo_CodigoFiscal ' +
        '                            where Mod_codigo in ' +
        '                                             (select Mod_codigo from ModeloNF ' +
        '                                               where Mod_Modelo like ' + QuotedStr('%CONHECIMENTO DE TRANSPORTE%') + ' ' +
        '                                                  or MOD_ESPECIE = ' + QuotedStr('CTRC') +
        '                                             ) ' +
        '                          )' +
        'group by ' +
        'b.Bkp_serie,b.Bkp_nota,b.ent_codigo,n.Notc_datafatura,e.ent_nome,n.Imp_NotaFiscal');
      p_q.parambyname('k_P_tipo').Asstring := 'S'
    end else begin
      p_q.sql.add('Select b.Bkp_serie,b.Bkp_nota,b.ent_codigo,n.Notc_datafatura,e.ent_nome,' +
        'n.Imp_NotaFiscal from Backup_NotaCompra b,notacompra n, entidade e where ' +
        'n.Notc_datafatura>=:k_P_data and n.Imp_NotaFiscal is null and ' +
        'b.notc_serie=n.notc_serie and b.notac_numero=n.notac_numero and ' +
        'b.ent_codigo=n.ent_codigo and n.ent_codigo=e.ent_codigo '+
        '            and Codf_Codigo in ' +
        '                          (select codf_codigo ' +
        '                             from Modelo_CodigoFiscal ' +
        '                            where Mod_codigo in ' +
        '                                             (select Mod_codigo from ModeloNF ' +
        '                                               where Mod_Modelo like ' + QuotedStr('%CONHECIMENTO DE TRANSPORTE%') + ' ' +
        '                                                  or MOD_ESPECIE = ' + QuotedStr('CTRC') +
        '                                             ) ' +
        '                          )' +
        'group by ' +
        'b.Bkp_serie,b.Bkp_nota,b.ent_codigo,n.Notc_datafatura,e.ent_nome,n.Imp_NotaFiscal');
    end;
    p_q.parambyname('k_P_data').AsDate := P_data;
  end;

end;

end.

